package top.ooyyh.dao;

import top.ooyyh.model.Task;
import top.ooyyh.util.DatabaseUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TaskDao {
    public boolean insertTask(Task task) {
        String sql = "INSERT INTO tasks (user_id, title, description, status, priority, due_date) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setInt(1, task.getUserId());
            ps.setString(2, task.getTitle());
            ps.setString(3, task.getDescription());
            ps.setString(4, task.getStatus());
            ps.setString(5, task.getPriority());
            ps.setString(6, task.getDueDate());
            int affected = ps.executeUpdate();
            if (affected > 0) {
                ResultSet rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    task.setId((int) rs.getLong(1));
                }
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updateTask(Task task) {
        String sql = "UPDATE tasks SET title=?, description=?, status=?, priority=?, due_date=? WHERE id=? AND user_id=?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, task.getTitle());
            ps.setString(2, task.getDescription());
            ps.setString(3, task.getStatus());
            ps.setString(4, task.getPriority());
            ps.setString(5, task.getDueDate());
            ps.setInt(6, task.getId());
            ps.setInt(7, task.getUserId());
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public List<Task> getTasksByUserId(int userId) {
        List<Task> list = new ArrayList<>();
        String sql = "SELECT t.*, u.username FROM tasks t JOIN users u ON t.user_id = u.id WHERE t.user_id=? ORDER BY t.created_at DESC";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Task t = new Task(
                        rs.getInt("id"),
                        rs.getInt("user_id"),
                        rs.getString("title"),
                        rs.getString("description"),
                        rs.getString("due_date"),
                        rs.getString("status"),
                        rs.getString("priority"),
                        rs.getString("username")
                );
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public Task getTaskById(int id, int userId) {
        String sql = "SELECT t.*, u.username FROM tasks t JOIN users u ON t.user_id = u.id WHERE t.id=? AND t.user_id=?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ps.setInt(2, userId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return new Task(
                        rs.getInt("id"),
                        rs.getInt("user_id"),
                        rs.getString("title"),
                        rs.getString("description"),
                        rs.getString("due_date"),
                        rs.getString("status"),
                        rs.getString("priority"),
                        rs.getString("username")
                );
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 新增：删除任务（基于任务ID和用户ID，确保权限）
    public boolean deleteTask(int id, int userId) {
        String sql = "DELETE FROM tasks WHERE id=? AND user_id=?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ps.setInt(2, userId);
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    // 新增：按用户统计任务（状态与优先级）
    public top.ooyyh.model.TaskStatistics getStatisticsByUserId(int userId) {
        String sql = "SELECT COUNT(*) AS total, "
                + "SUM(CASE WHEN status='TODO' THEN 1 ELSE 0 END) AS todo, "
                + "SUM(CASE WHEN status='IN_PROGRESS' THEN 1 ELSE 0 END) AS in_progress, "
                + "SUM(CASE WHEN status='DONE' THEN 1 ELSE 0 END) AS done, "
                + "SUM(CASE WHEN priority='LOW' THEN 1 ELSE 0 END) AS low, "
                + "SUM(CASE WHEN priority='MEDIUM' THEN 1 ELSE 0 END) AS medium, "
                + "SUM(CASE WHEN priority='HIGH' THEN 1 ELSE 0 END) AS high "
                + "FROM tasks WHERE user_id=?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                top.ooyyh.model.TaskStatistics s = new top.ooyyh.model.TaskStatistics();
                s.setTotal(rs.getInt("total"));
                s.setTodo(rs.getInt("todo"));
                s.setInProgress(rs.getInt("in_progress"));
                s.setDone(rs.getInt("done"));
                s.setLow(rs.getInt("low"));
                s.setMedium(rs.getInt("medium"));
                s.setHigh(rs.getInt("high"));
                return s;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 返回一个默认的统计对象（全部为0），避免上层空检查问题
        return new top.ooyyh.model.TaskStatistics();
    }
}
